package co.gov.medellin.ssti.sub.paoyer.administration;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import javax.naming.NamingException;

import co.gov.medellin.ssti.sub.common.excepcion.SUBException;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.ApplicationDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.ApplicationUserDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.AuthorizedByBranchDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.CostCenterByAuthorizedDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.FDClassDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.FDDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.FDTypeDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.FlexValueDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.OptionDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.ProgramDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.ProgramParameterDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.UserDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.UserGroupDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.UserGroupOptionDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.dao.WarehouseByBranchDAO;
import co.gov.medellin.ssti.sub.paoyer.administration.exception.AdministrationUserException;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.ApplicationUserVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.ApplicationVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.AuthorizedByBranchVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.ContractVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.CostCenterByAuthorizedVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.FDClassVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.FDTypeVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.FDVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.FlexValueVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.OptionVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.ProgramParameterVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.ProgramVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.UserGroupOptionVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.UserGroupVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.UserVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.WarehouseByBranchVO;
import co.gov.medellin.ssti.sub.paoyer.common.DataSourceFactory;
import co.gov.medellin.ssti.sub.paoyer.common.exception.CommonSystemException;
import co.gov.medellin.ssti.sub.paoyer.common.exception.OracleKnownException;
import co.gov.medellin.ssti.sub.paoyer.common.exception.SystemException;
import co.gov.medellin.ssti.sub.paoyer.common.exception.UserException;
import co.gov.medellin.ssti.sub.paoyer.common.validator.FieldValidator;

public class AdministrationMngr {
	public AdministrationMngr() {
	}

	public String getMenu(String strUserGroup, String strPath)
			throws UserException, SystemException {
		Connection conn = null;

		try {
			conn = DataSourceFactory.getConnection();
			UserDAO userDAO = new UserDAO(conn);
			return userDAO.getMenu(strUserGroup, strPath);
		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public UserVO userGroup(UserVO criterea) throws UserException,
			SystemException {
		Connection conn = null;

		try {
			conn = DataSourceFactory.getConnection();
			UserDAO userDAO = new UserDAO(conn);
			UserVO groupUser = userDAO.userGroup(criterea);

			return groupUser;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Vector<String> getResources(UserVO criterea) throws UserException,
			SystemException {
		Connection conn = null;

		try {
			conn = DataSourceFactory.getConnection();
			UserDAO userDAO = new UserDAO(conn);
			Vector<String> resources = userDAO.getResources(criterea);

			return resources;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	// Tabla TCS_USUARIO
	public UserVO authenticateUser(UserVO criterea) throws UserException,
			SystemException {
		Connection conn = null;

		FieldValidator.validateString(criterea.getName());
		// FieldValidator.validateString(criterea.getPassword());
		try {
			conn = DataSourceFactory.getConnection();
			UserDAO userDAO = new UserDAO(conn);
			UserVO foundUser = userDAO.authenticateUser(criterea);

			if (foundUser == null) {
				throw new UserException(
						new AdministrationUserException().getModuleVO(),
						AdministrationUserException.WRONG_PASSWORD);
			}

			return foundUser;

		} catch (NamingException ne) {
			SUBException.logDebugError("", ne);
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			SUBException.logDebugError("", se);
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public boolean createUser(UserVO user) throws UserException,
			SystemException {
		Connection conn = null;
		UserDAO userDAO = null;
		FieldValidator.validateString(user.getName());
		FieldValidator.validateString(user.getDescription());
		FieldValidator.validateString(user.getUse());
		FieldValidator.validateNumber(user.getUserGroupCode().doubleValue());

		try {
			conn = DataSourceFactory.getConnection();
			userDAO = new UserDAO(conn);
			return userDAO.createUser(user);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			SUBException.logDebugError("", se);
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				userDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return false;
			}
		}
	}

	public boolean updateUser(UserVO user) throws UserException,
			SystemException {
		Connection conn = null;
		UserDAO userDAO = null;
		FieldValidator.validateNumber(user.getCodeArea().doubleValue());
		FieldValidator.validateString(user.getName());
		FieldValidator.validateString(user.getDescription());
		FieldValidator.validateString(user.getUse());
		FieldValidator.validateNumber(user.getUserGroupCode().doubleValue());

		try {
			conn = DataSourceFactory.getConnection();
			userDAO = new UserDAO(conn);
			return userDAO.updateUser(user);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			// se.printStackTrace();
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				userDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return false;
			}
		}
	}

	public boolean deleteUser(UserVO user) throws UserException,
			SystemException {
		Connection conn = null;
		UserDAO userDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			userDAO = new UserDAO(conn);
			return userDAO.deleteUser(user);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				userDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return false;
			}
		}
	}

	public boolean activateUser(UserVO user) throws UserException,
			SystemException {
		Connection conn = null;
		UserDAO userDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			userDAO = new UserDAO(conn);
			return userDAO.activateUser(user);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				userDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return false;
			}
		}
	}

	public boolean desactivateUser(UserVO user) throws UserException,
			SystemException {
		Connection conn = null;
		UserDAO userDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			userDAO = new UserDAO(conn);
			return userDAO.activateUser(user);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				userDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return false;
			}
		}
	}

	public Collection<UserVO> findUserByCritereaAdmin(UserVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserDAO userDAO = null;
		try {
			conn = DataSourceFactory.getConnection();
			userDAO = new UserDAO(conn);
			return userDAO.findUserByCritereaAdmin(criterea);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			SUBException.logDebugError("", se);
			OracleKnownException.traslateSQLException(se);

			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				userDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return null;
			}
		}
	}

	public Collection<UserVO> findUserByCriterea(UserVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserDAO userDAO = null;
		try {
			conn = DataSourceFactory.getConnection();
			userDAO = new UserDAO(conn);
			return userDAO.findUserByCriterea(criterea);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			SUBException.logDebugError("", se);
			OracleKnownException.traslateSQLException(se);

			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				userDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return null;
			}
		}
	}

	// Metodos para la tabla TCS_APLICACION

	public Collection<ApplicationVO> findApplicationByCriterea(
			ApplicationVO criterea) throws UserException, SystemException {
		Connection conn = null;
		ApplicationDAO applicationDAO = null;
		try {
			conn = DataSourceFactory.getConnection();
			applicationDAO = new ApplicationDAO(conn);
			return applicationDAO.findApplicationByCriterea(criterea);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				applicationDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return null;
			}
		}
	}

	public ApplicationVO createApplication(ApplicationVO application)
			throws UserException, SystemException {
		Connection conn = null;
		ApplicationDAO applicationDAO = null;
		FieldValidator.validateString(application.getName());
		FieldValidator.validateString(application.getDescription());
		FieldValidator.validateString(application.getAbbreviation());
		FieldValidator.validateString(application.getUse());
		FieldValidator.validateNumber(application.getOwnerUserCode()
				.doubleValue());
		FieldValidator.validateNumber(application.getUserCode().doubleValue());

		try {
			conn = DataSourceFactory.getConnection();
			applicationDAO = new ApplicationDAO(conn);
			return applicationDAO.createApplication(application);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				applicationDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return null;
			}
		}
	}

	public int updateApplication(ApplicationVO application)
			throws UserException, SystemException {
		Connection conn = null;
		ApplicationDAO applicationDAO = null;
		FieldValidator.validateString(application.getName());
		FieldValidator.validateString(application.getDescription());
		FieldValidator.validateString(application.getAbbreviation());
		FieldValidator.validateString(application.getUse());
		FieldValidator.validateNumber(application.getCode().doubleValue());
		FieldValidator.validateNumber(application.getOwnerUserCode()
				.doubleValue());
		FieldValidator.validateNumber(application.getUserCode().doubleValue());

		try {
			conn = DataSourceFactory.getConnection();
			applicationDAO = new ApplicationDAO(conn);
			return applicationDAO.updateApplication(application);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return -1;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				applicationDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return -1;
			}
		}
	}

	public int deleteApplication(ApplicationVO application)
			throws UserException, SystemException {
		Connection conn = null;
		ApplicationDAO applicationDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			applicationDAO = new ApplicationDAO(conn);
			return applicationDAO.deleteApplication(application);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return -1;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				applicationDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return -1;
			}
		}
	}

	public UserGroupVO createUserGroup(UserGroupVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserGroupDAO userGroupDAO = null;

		FieldValidator.validateString(criterea.getName());
		// FieldValidator.validateString(criterea.getUse());
		// FieldValidator.validateString(criterea.getAdminCtrlInd());
		// FieldValidator.validateString(criterea.getAuditCtrlInd());
		// FieldValidator.validateString(criterea.getBlockCtrlInd());
		// FieldValidator.validateString(criterea.getRecLockInd());
		try {
			conn = DataSourceFactory.getConnection();
			userGroupDAO = new UserGroupDAO(conn);
			UserGroupVO newUserGroup = userGroupDAO.createUserGroup(criterea);

			return newUserGroup;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				userGroupDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<UserGroupVO> findUserGroupByCriterea(UserGroupVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserGroupDAO userGroupDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			userGroupDAO = new UserGroupDAO(conn);
			Collection<UserGroupVO> foundUserGroup = userGroupDAO
					.findUserGroupByCriterea(criterea);

			return foundUserGroup;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				userGroupDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public UserGroupVO updateUserGroup(UserGroupVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserGroupDAO userGroupDAO = null;

		FieldValidator.validateString(criterea.getName());
		FieldValidator.validateString(criterea.getDescription());
		// FieldValidator.validateString(criterea.getUse());
		// FieldValidator.validateString(criterea.getAdminCtrlInd());
		// FieldValidator.validateString(criterea.getAuditCtrlInd());
		// FieldValidator.validateString(criterea.getBlockCtrlInd());
		// FieldValidator.validateString(criterea.getRecLockInd());
		try {
			conn = DataSourceFactory.getConnection();
			userGroupDAO = new UserGroupDAO(conn);
			UserGroupVO updatedUserGroup = userGroupDAO
					.updateUserGroup(criterea);

			return updatedUserGroup;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				userGroupDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public void deleteUserGroup(UserGroupVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		UserGroupDAO userGroupDAO = null;
		FieldValidator.validateNumber(criterea.getCode().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			userGroupDAO = new UserGroupDAO(conn);
			boolean deletes = userGroupDAO.deleteUserGroup(criterea);

			if (deletes == true) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
		} finally {
			try {
				userGroupDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de las opciones */

	public OptionVO createOption(OptionVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		OptionDAO optionDAO = null;

		FieldValidator.validateString(criterea.getName());
		FieldValidator.validateString(criterea.getOptionType());
		FieldValidator.validateNumber(criterea.getOrder().doubleValue());

		try {
			conn = DataSourceFactory.getConnection();
			optionDAO = new OptionDAO(conn);
			OptionVO newOption = optionDAO.createOption(criterea);

			return newOption;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				optionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<OptionVO> findOptionByCriterea(OptionVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		OptionDAO optionDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			optionDAO = new OptionDAO(conn);
			Collection<OptionVO> foundOption = optionDAO
					.findOptionByCriterea(criterea);

			return foundOption;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				optionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public OptionVO updateOption(OptionVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		OptionDAO optionDAO = null;

		FieldValidator.validateString(criterea.getName());
		FieldValidator.validateString(criterea.getOptionType());
		FieldValidator.validateNumber(criterea.getOrder().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			optionDAO = new OptionDAO(conn);
			OptionVO updatedOption = optionDAO.updateOption(criterea);

			return updatedOption;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				optionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public void deleteOption(OptionVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		OptionDAO optionDAO = null;
		FieldValidator.validateNumber(criterea.getCode().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			optionDAO = new OptionDAO(conn);
			boolean deletes = optionDAO.deleteOption(criterea);

			if (deletes == true) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
		} finally {
			try {
				optionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de los programas */

	public ProgramVO createProgram(ProgramVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		ProgramDAO programDAO = null;

		FieldValidator.validateString(criterea.getName());
		FieldValidator.validateString(criterea.getProgramType());
		/*
		 * FieldValidator.validateString(criterea.getCsEstandar());
		 * FieldValidator.validateString(criterea.getUse());
		 */

		try {
			conn = DataSourceFactory.getConnection();
			programDAO = new ProgramDAO(conn);
			ProgramVO newProgram = programDAO.createProgram(criterea);

			return newProgram;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				programDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<ProgramVO> findProgramByCriterea(ProgramVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		ProgramDAO programDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			programDAO = new ProgramDAO(conn);
			Collection<ProgramVO> foundProgram = programDAO
					.findProgramByCriterea(criterea);

			return foundProgram;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				programDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public ProgramVO updateProgram(ProgramVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		ProgramDAO programDAO = null;

		FieldValidator.validateString(criterea.getName());
		FieldValidator.validateString(criterea.getProgramType());
		/*
		 * FieldValidator.validateString(criterea.getCsEstandar());
		 * FieldValidator.validateString(criterea.getUse());
		 */

		try {
			conn = DataSourceFactory.getConnection();
			programDAO = new ProgramDAO(conn);
			ProgramVO updatedProgram = programDAO.updateProgram(criterea);

			return updatedProgram;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				programDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public void deleteProgram(ProgramVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		ProgramDAO programDAO = null;
		FieldValidator.validateNumber(criterea.getCode().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			programDAO = new ProgramDAO(conn);
			boolean deletes = programDAO.deleteProgram(criterea);

			if (deletes == true) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
		} finally {
			try {
				programDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de parametros de programas */

	public ProgramParameterVO createProgramParameter(ProgramParameterVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		ProgramParameterDAO programParameterDAO = null;

		FieldValidator.validateString(criterea.getName());
		FieldValidator.validateString(criterea.getParameterType());
		FieldValidator.validateString(criterea.getMandatory());
		FieldValidator.validateString(criterea.getDataType());
		FieldValidator.validateNumber(criterea.getOrder().doubleValue());
		FieldValidator.validateString(criterea.getLabel());
		FieldValidator.validateString(criterea.getUse());
		try {
			conn = DataSourceFactory.getConnection();
			programParameterDAO = new ProgramParameterDAO(conn);
			ProgramParameterVO newProgramParameter = programParameterDAO
					.createProgramParameter(criterea);

			return newProgramParameter;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				programParameterDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<ProgramParameterVO> findProgramParameterByCriterea(
			ProgramParameterVO criterea) throws UserException, SystemException {
		Connection conn = null;
		ProgramParameterDAO programParameterDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			programParameterDAO = new ProgramParameterDAO(conn);
			Collection<ProgramParameterVO> foundProgramParameter = programParameterDAO
					.findProgramParameterByCriterea(criterea);

			return foundProgramParameter;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				programParameterDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public ProgramParameterVO updateProgramParameter(ProgramParameterVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		ProgramParameterDAO programParameterDAO = null;

		FieldValidator.validateString(criterea.getName());
		FieldValidator.validateString(criterea.getParameterType());
		FieldValidator.validateString(criterea.getMandatory());
		FieldValidator.validateString(criterea.getDataType());
		FieldValidator.validateNumber(criterea.getOrder().doubleValue());
		FieldValidator.validateString(criterea.getLabel());
		FieldValidator.validateString(criterea.getUse());
		try {
			conn = DataSourceFactory.getConnection();
			programParameterDAO = new ProgramParameterDAO(conn);
			ProgramParameterVO updatedProgramParameter = programParameterDAO
					.updateProgramParameter(criterea);

			return updatedProgramParameter;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				programParameterDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public void deleteProgramParameter(ProgramParameterVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		ProgramParameterDAO programParameterDAO = null;
		FieldValidator.validateNumber(criterea.getCode().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			programParameterDAO = new ProgramParameterDAO(conn);
			boolean deletes = programParameterDAO
					.deleteProgramParameter(criterea);

			if (deletes == true) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
		} finally {
			try {
				programParameterDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de opciones-grupos de usuarios */

	public UserGroupOptionVO createUserGroupOption(UserGroupOptionVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserGroupOptionDAO userGroupOptionDAO = null;

		FieldValidator
				.validateNumber(criterea.getUserGroupCode().doubleValue());
		FieldValidator.validateNumber(criterea.getOptionCode().doubleValue());
		FieldValidator.validateString(criterea.getAllowQuery());
		FieldValidator.validateString(criterea.getAllowUpdate());
		FieldValidator.validateString(criterea.getAllowInsert());
		FieldValidator.validateString(criterea.getAllowDelete());
		try {
			conn = DataSourceFactory.getConnection();
			userGroupOptionDAO = new UserGroupOptionDAO(conn);
			UserGroupOptionVO newUserGroupOption = userGroupOptionDAO
					.createUserGroupOption(criterea);

			return newUserGroupOption;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				userGroupOptionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<UserGroupOptionVO> findUserGroupOptionByCriterea(
			UserGroupOptionVO criterea) throws UserException, SystemException {
		Connection conn = null;
		UserGroupOptionDAO userGroupOptionDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			userGroupOptionDAO = new UserGroupOptionDAO(conn);
			Collection<UserGroupOptionVO> foundUserGroupOption = userGroupOptionDAO
					.findUserGroupOptionByCriterea(criterea);

			return foundUserGroupOption;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				userGroupOptionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<UserGroupOptionVO> findNotAsociatedOptionByCriterea(
			UserGroupOptionVO criterea) throws UserException, SystemException {
		Connection conn = null;
		UserGroupOptionDAO userGroupOptionDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			userGroupOptionDAO = new UserGroupOptionDAO(conn);
			Collection<UserGroupOptionVO> foundUserGroupOption = userGroupOptionDAO
					.findNotAsociatedOptionByCriterea(criterea);

			return foundUserGroupOption;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				userGroupOptionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public UserGroupOptionVO updateUserGroupOption(UserGroupOptionVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserGroupOptionDAO userGroupOptionDAO = null;

		FieldValidator
				.validateNumber(criterea.getUserGroupCode().doubleValue());
		FieldValidator.validateNumber(criterea.getOptionCode().doubleValue());
		FieldValidator.validateString(criterea.getAllowQuery());
		FieldValidator.validateString(criterea.getAllowUpdate());
		FieldValidator.validateString(criterea.getAllowInsert());
		FieldValidator.validateString(criterea.getAllowDelete());
		try {
			conn = DataSourceFactory.getConnection();
			userGroupOptionDAO = new UserGroupOptionDAO(conn);
			UserGroupOptionVO updatedUserGroupOption = userGroupOptionDAO
					.updateUserGroupOption(criterea);

			return updatedUserGroupOption;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				userGroupOptionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public boolean asociateOptionToUserGroup(UserGroupOptionVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserGroupOptionDAO userGroupOptionDAO = null;
		FieldValidator
				.validateNumber(criterea.getUserGroupCode().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			userGroupOptionDAO = new UserGroupOptionDAO(conn);
			return userGroupOptionDAO.asociateOptionToUserGroup(criterea);
		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
		} finally {
			try {
				userGroupOptionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
		return true;
	}

	public void deleteUserGroupOption(UserGroupOptionVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		UserGroupOptionDAO userGroupOptionDAO = null;
		FieldValidator.validateNumber(criterea.getCode().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			userGroupOptionDAO = new UserGroupOptionDAO(conn);
			boolean deletes = userGroupOptionDAO
					.deleteUserGroupOption(criterea);

			if (deletes == true) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
		} finally {
			try {
				userGroupOptionDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de Usuarios-Aplicacion */

	public Collection<ApplicationUserVO> findUserNotAsociatedByCriterea(
			ApplicationUserVO criterea) throws UserException, SystemException {
		Connection conn = null;
		ApplicationUserDAO userDAO = null;
		try {
			conn = DataSourceFactory.getConnection();
			userDAO = new ApplicationUserDAO(conn);
			return userDAO.findUserNotAsociatedByCriterea(criterea);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				userDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return null;
			}
		}
	}

	public ApplicationUserVO createApplicationUser(ApplicationUserVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		ApplicationUserDAO applicationUserDAO = null;

		FieldValidator.validateNumber(criterea.getUserCode().doubleValue());
		FieldValidator.validateNumber(criterea.getApplicationCode()
				.doubleValue());
		FieldValidator.validateString(criterea.getUse());
		try {
			conn = DataSourceFactory.getConnection();
			applicationUserDAO = new ApplicationUserDAO(conn);
			ApplicationUserVO newApplicationUser = applicationUserDAO
					.createApplicationUser(criterea);

			return newApplicationUser;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				applicationUserDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<ApplicationUserVO> findApplicationUserByCriterea(
			ApplicationUserVO criterea) throws UserException, SystemException {
		Connection conn = null;
		ApplicationUserDAO applicationUserDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			applicationUserDAO = new ApplicationUserDAO(conn);
			Collection<ApplicationUserVO> foundApplicationUser = applicationUserDAO
					.findApplicationUserByCriterea(criterea);

			return foundApplicationUser;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				applicationUserDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public ApplicationUserVO updateApplicationUser(ApplicationUserVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		ApplicationUserDAO applicationUserDAO = null;

		FieldValidator.validateNumber(criterea.getUserCode().doubleValue());
		FieldValidator.validateNumber(criterea.getApplicationCode()
				.doubleValue());
		FieldValidator.validateString(criterea.getUse());
		try {
			conn = DataSourceFactory.getConnection();
			applicationUserDAO = new ApplicationUserDAO(conn);
			ApplicationUserVO updatedApplicationUser = applicationUserDAO
					.updateApplicationUser(criterea);

			return updatedApplicationUser;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				applicationUserDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public void deleteApplicationUser(ApplicationUserVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		ApplicationUserDAO applicationUserDAO = null;
		FieldValidator.validateNumber(criterea.getCode().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			applicationUserDAO = new ApplicationUserDAO(conn);
			boolean deletes = applicationUserDAO
					.deleteApplicationUser(criterea);

			if (deletes == false) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
		} finally {
			try {
				applicationUserDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	// Metodos de consulta

	public Collection<FDVO> findInventoryByCriterea(FDVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		FDDAO fdDAO = null;
		try {
			conn = DataSourceFactory.getConnection();
			fdDAO = new FDDAO(conn);
			return fdDAO.findInventoryByCriterea(criterea);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				fdDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return null;
			}
		}
	}

	public Collection<ContractVO> findContractByCriterea(ContractVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		FDDAO fdDAO = null;
		try {
			conn = DataSourceFactory.getConnection();
			fdDAO = new FDDAO(conn);
			return fdDAO.findContractxAuthorizedByCriterea(criterea);

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				fdDAO = null;
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
				OracleKnownException.traslateSQLException(e);
				return null;
			}
		}
	}

	/* Metodos para el manejo de Sucursales por Autorizado */

	public Collection<AuthorizedByBranchVO> findAuthorizedBranchByCriterea(
			AuthorizedByBranchVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		AuthorizedByBranchDAO authorizedByBranchDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			authorizedByBranchDAO = new AuthorizedByBranchDAO(conn);
			Collection<AuthorizedByBranchVO> foundAuthorizedByBranch = authorizedByBranchDAO
					.findAuthorizedBranchByCriterea(criterea);

			return foundAuthorizedByBranch;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				authorizedByBranchDAO = null;
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de Bodegas por Sucursal */

	public Collection<WarehouseByBranchVO> findWarehouseBranchByCriterea(
			WarehouseByBranchVO criterea) throws UserException, SystemException {
		Connection conn = null;
		WarehouseByBranchDAO warehouseByBranchDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			warehouseByBranchDAO = new WarehouseByBranchDAO(conn);
			Collection<WarehouseByBranchVO> foundWarehouseByBranch = warehouseByBranchDAO
					.findWarehouseBranchByCriterea(criterea);

			return foundWarehouseByBranch;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		}

		catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				warehouseByBranchDAO = null;
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de Centros de Costo por Autorizado */

	public Collection<CostCenterByAuthorizedVO> findCostCenterAuthorizedByCriterea(
			CostCenterByAuthorizedVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		CostCenterByAuthorizedDAO costCenterByAuthorizedDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			costCenterByAuthorizedDAO = new CostCenterByAuthorizedDAO(conn);
			Collection<CostCenterByAuthorizedVO> foundCostCenterByAuthorized = costCenterByAuthorizedDAO
					.findCostCenterAuthorizedByCriterea(criterea);

			return foundCostCenterByAuthorized;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				costCenterByAuthorizedDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de Clases de Fondos Documentales */
	public Collection<FDClassVO> findFDClassByCriterea(FDClassVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		FDClassDAO fdClassDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			fdClassDAO = new FDClassDAO(conn);
			Collection<FDClassVO> foundFDClass = fdClassDAO
					.findFDClassByCriterea(criterea);

			return foundFDClass;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				fdClassDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de Tipos de Fondos Documentales */
	public Collection<FDTypeVO> findFDTypeByCriterea(FDTypeVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		FDTypeDAO fdTypeDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			fdTypeDAO = new FDTypeDAO(conn);
			Collection<FDTypeVO> foundFDType = fdTypeDAO
					.findFDTypeByCriterea(criterea);

			return foundFDType;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				fdTypeDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para la Consulta Historica de Fondos Documentales */
	public Collection<FDVO> findFDHistoryByCriterea(FDVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		FDDAO fdDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			fdDAO = new FDDAO(conn);
			Collection<FDVO> foundFD = fdDAO.findFDHistoryByCriterea(criterea);

			return foundFD;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				fdDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	/* Metodos para el manejo de Valores Flexibles */

	public FlexValueVO createFlexValue(FlexValueVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		FlexValueDAO flexValueDAO = null;

		FieldValidator.validateString(criterea.getFlexValueName());
		FieldValidator.validateString(criterea.getFlexValueUse());
		try {
			conn = DataSourceFactory.getConnection();
			flexValueDAO = new FlexValueDAO(conn);
			FlexValueVO newFlexValue = flexValueDAO.createFlexValue(criterea);

			return newFlexValue;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				flexValueDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<FlexValueVO> findFlexValueByCriterea(FlexValueVO criterea)
			throws UserException, SystemException {
		Connection conn = null;
		FlexValueDAO flexValueDAO = null;

		try {
			conn = DataSourceFactory.getConnection();
			flexValueDAO = new FlexValueDAO(conn);
			Collection<FlexValueVO> foundFlexValue = flexValueDAO
					.findFlexValueByCriterea(criterea);

			return foundFlexValue;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				flexValueDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public boolean updateFlexValue(FlexValueVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		FlexValueDAO flexValueDAO = null;

		FieldValidator.validateString(criterea.getFlexValueName());
		// FieldValidator.validateString(criterea.getFlexValueUse());
		try {
			conn = DataSourceFactory.getConnection();
			flexValueDAO = new FlexValueDAO(conn);
			boolean update = flexValueDAO.updateFlexValue(criterea);

			if (update == false) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}
			return update;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				flexValueDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public boolean deleteFlexValue(FlexValueVO criterea) throws UserException,
			SystemException {
		Connection conn = null;
		FlexValueDAO flexValueDAO = null;
		FieldValidator.validateNumber(criterea.getFlexValueInternalCode()
				.doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			flexValueDAO = new FlexValueDAO(conn);
			boolean deletes = flexValueDAO.deleteFlexValue(criterea);

			if (deletes == false) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}
			return deletes;
		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				flexValueDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public Collection<FlexValueVO> findFlexValueTypeByCriterea(
			FlexValueVO criterea) throws UserException, SystemException {
		Connection conn = null;
		FlexValueDAO flexValueDAO = null;
		try {
			conn = DataSourceFactory.getConnection();
			flexValueDAO = new FlexValueDAO(conn);
			Collection<FlexValueVO> foundFlexValueTypes = flexValueDAO
					.findFlexValueTypeByCriterea(criterea);

			return foundFlexValueTypes;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				flexValueDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public FlexValueVO createFlexValueType(FlexValueVO flexValueType)
			throws UserException, SystemException {
		Connection conn = null;
		FlexValueDAO flexValueDAO = null;

		FieldValidator.validateString(flexValueType.getFlexValueTypeName());
		FieldValidator.validateString(flexValueType
				.getFlexValueTypeAutomaticCustomCode());
		// FieldValidator.validateString(flexValueType.getFlexValueTypeUse());
		FieldValidator.validateNumber(flexValueType
				.getApplicationContextInternalCode().doubleValue());

		try {
			conn = DataSourceFactory.getConnection();
			flexValueDAO = new FlexValueDAO(conn);
			FlexValueVO newFlexValueType = flexValueDAO
					.createFlexValueType(flexValueType);

			return newFlexValueType;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return null;
		} finally {
			try {
				flexValueDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public boolean updateFlexValueType(FlexValueVO flexValueType)
			throws UserException, SystemException {
		Connection conn = null;
		FlexValueDAO flexValueDAO = null;

		FieldValidator.validateString(flexValueType.getFlexValueTypeName());
		try {
			conn = DataSourceFactory.getConnection();
			flexValueDAO = new FlexValueDAO(conn);
			boolean update = flexValueDAO.updateFlexValueType(flexValueType);

			if (update == false) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}
			return update;

		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				flexValueDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

	public boolean deleteFlexValueType(FlexValueVO flexValueType)
			throws UserException, SystemException {
		Connection conn = null;
		FlexValueDAO flexValueDAO = null;
		FieldValidator.validateNumber(flexValueType
				.getFlexValueTypeInternalCode().doubleValue());
		try {
			conn = DataSourceFactory.getConnection();
			flexValueDAO = new FlexValueDAO(conn);
			boolean deletes = flexValueDAO.deleteFlexValueType(flexValueType);

			if (deletes == false) {
				throw new SystemException(
						new CommonSystemException().getModuleVO(),
						CommonSystemException.QUERY_EXCEPTION);
			}
			return deletes;
		} catch (NamingException ne) {
			throw new SystemException(
					new CommonSystemException().getModuleVO(),
					CommonSystemException.JAVA_EXCEPTION_OCCURRED, ne,
					ne.getMessage());
		} catch (SQLException se) {
			OracleKnownException.traslateSQLException(se);
			return false;
		} finally {
			try {
				flexValueDAO = null;
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				SUBException.logDebugError("", e);
			}
		}
	}

}